package ssau.knyazev.cafe.model;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import ssau.knyazev.common.UniException;
import ssau.knyazev.db.DBManager;
import ssau.knyazev.db.core.ConnectionHelper;



public class OrderRecord {

	private String orderID;
	private String dishID;
	private String dishName;
	private String count;
	private final static String loadQuery = "select cp.order_id, cp.dish_id, cd.name, cp.count " +
			"from cafe_portions cp, cafe_dishes cd " +
			"where cp.order_id = ? and cp.dish_id = ? and cp.dish_id = cd.dish_id";
	private final static String saveQuery = "call cafe_record_order(?,?,?,?)";
	private static final String loadAllQuery = "select cp.order_id, cp.dish_id, cd.name, cp.count " +
			"from cafe_portions cp, cafe_dishes cd " +
			"where cp.order_id = ? and cp.dish_id = cd.dish_id";
	
	
	public String getOrderID() {
		return orderID;
	}

	public void setOrderID(String orderID) {
		this.orderID = orderID;
	}

	public String getDishID() {
		return dishID;
	}

	public void setDishID(String dishID) {
		this.dishID = dishID;
	}

	public String getDishName() {
		return dishName;
	}

	public void setDishName(String dishName) {
		this.dishName = dishName;
	}

	public String getCount() {
		return count;
	}

	public void setCount(String count) {
		this.count = count;
	}

	public static OrderRecord load(String orderID, String dishID)
			throws UniException {
		Connection con = ConnectionHelper.getConnection();
		PreparedStatement state = null;
		OrderRecord result = null;
		try {
			state = con.prepareStatement(loadQuery);
			state.setString(1, orderID);
			state.setString(2, dishID);
			ResultSet rs = state.executeQuery();
			if (rs.next()) {
				result = new OrderRecord();
				result.setOrderID(rs.getString(1));
				result.setDishID(rs.getString(2));
				result.setDishName(rs.getString(3));
				result.setCount(rs.getString(4));

				return result;
			} else {
				throw new UniException("Not found order_record with orderID = "
						+ orderID + " and dishID = " + dishID);
			}
		} catch (SQLException e) {
			throw new UniException("Could not perform query:\n" + loadQuery, e);
		} finally {
			ConnectionHelper.closeConnection(con, state);
		}
	}
	
	public static List<OrderRecord> loadAll(String orderID) throws UniException{
		List<OrderRecord> ors = new LinkedList<OrderRecord>();
		Connection con = ConnectionHelper.getConnection();
		PreparedStatement state = null;
		try {
			state = con.prepareStatement(loadAllQuery);
			state.setString(1, orderID);
			ResultSet rs = state.executeQuery();
			while (rs.next()){
				OrderRecord or = new OrderRecord();
				or.setOrderID(orderID);
				or.setDishID(rs.getString(2));
				or.setDishName(rs.getString(3));
				or.setCount(rs.getString(4));
				ors.add(or);
			}
			return ors;
		} catch (SQLException e) {
			throw new UniException("Could not perform query:\n" + loadQuery, e);
		} finally {
			ConnectionHelper.closeConnection(con, state);
		}
	}
	
	public void save() throws UniException{
		Order o = Order.load(orderID);
		Connection con = ConnectionHelper.getConnection();
		CallableStatement state = null;
		try {
			System.out.println("Saving... " + orderID + " | " + dishID + " | " + DBManager.getInst().getUserID() + " | " +  count);
			state = con.prepareCall(saveQuery);
			state.setString(1, orderID);
			state.setString(2, o.getUserID());
			state.setString(3, dishID);
			state.setString(4, count);
			
			state.executeUpdate();
			System.out.println("saved");
		} catch (SQLException e) {
			throw new UniException("Could not perform query:\n" + saveQuery, e);
		} finally {
			ConnectionHelper.closeConnection(con, state);
		}
	}

	public String toString() {
		return count;
	}
}
